﻿using System;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;
using System.IO;
using System.Text;

namespace SqlCeEditor
{
    /// <summary>
    /// Keep it static for now
    /// </summary>
    static internal class dbHandler
    {
        /// <summary>
        /// Database connection
        /// </summary>
        private static SqlCeConnection dbConn = new SqlCeConnection();

        public static string dbPass = string.Empty;






        /// <summary>
        /// Fullpath filename of the database
        /// </summary>
        public static string dbFilename
        {
            get
            {
                return dbConn.Database;
            }

        }


        /// <summary>
        /// Is connection open to read?
        /// </summary>
        public static bool isConnectionOpen
        {
            get
            {
                return (dbConn.State == ConnectionState.Open);
            }


        }


        private static SqlCeCommand sqlGetTables = null;
        private static SqlCeCommand sqlGetIndexes = null;
        private static SqlCeCommand sqlGetAllTablesInfo = null;
        private static SqlCeCommand sqlGetAllIndexesInfo = null;
        private static SqlCeCommand sqlGetAllKeysInfo = null;
        private static SqlCeCommand sqlGetAllConstraintsInfo = null;
        private static SqlCeCommand getColumnType = null;
        private static SqlCeCommand getIndexInfo = null;
        private static SqlCeCommand getAllColumns = null;
        private static SqlCeCommand getAllColumnInfo = null;

        /// <summary>
        /// Create database procedures
        /// </summary>
        private static void PrepareDbCommands()
        {

            sqlGetTables = dbConn.CreateCommand();
            sqlGetTables.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
            sqlGetTables.Prepare();


            sqlGetIndexes = dbConn.CreateCommand();
            sqlGetIndexes.CommandText = "SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME, PRIMARY_KEY FROM INFORMATION_SCHEMA.INDEXES";
            sqlGetIndexes.Prepare();

            sqlGetAllTablesInfo = dbConn.CreateCommand();
            sqlGetAllTablesInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
            sqlGetAllTablesInfo.Prepare();


            sqlGetAllIndexesInfo = dbConn.CreateCommand();
            sqlGetAllIndexesInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.INDEXES";
            sqlGetAllIndexesInfo.Prepare();


            sqlGetAllKeysInfo = dbConn.CreateCommand();
            sqlGetAllKeysInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
            sqlGetAllKeysInfo.Prepare();


            sqlGetAllConstraintsInfo = dbConn.CreateCommand();
            sqlGetAllConstraintsInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS"; ;
            sqlGetAllConstraintsInfo.Prepare();


            getColumnType = dbConn.CreateCommand();
            getColumnType.CommandText = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
            getColumnType.Prepare();


            getIndexInfo = dbConn.CreateCommand();
            getIndexInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE INDEX_NAME = ?";
            getIndexInfo.Prepare();


            getAllColumns = dbConn.CreateCommand();


            getAllColumns.CommandText =
            @"SELECT ix.PRIMARY_KEY, 
 ic.* FROM INFORMATION_SCHEMA.COLUMNS IC LEFT JOIN 
(SELECT COLUMN_NAME, PRIMARY_KEY FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = ? and primary_key = 1) ix 
ON ic.column_name = ix.column_name WHERE ic.table_name = ?";
          
            getAllColumns.Prepare();


            getAllColumnInfo = dbConn.CreateCommand();

            getAllColumnInfo.CommandText = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?";
            getAllColumnInfo.Prepare();

        }



        public static bool OpenConnection(string fileName)
        {

            string ceConnectionString = string.Empty;

            try
            {
                if (string.IsNullOrEmpty(dbPass))
                    ceConnectionString = string.Format("Data Source='{0}'; LCID=1033;", fileName);
                else
                    ceConnectionString = string.Format("Data Source='{0}'; LCID=1033; Password = '{1}'", fileName, dbPass);

                dbConn = new SqlCeConnection(ceConnectionString);
                dbConn.Open();

                PrepareDbCommands();

                return true;
            }
            catch (SqlCeException ex)
            {
                //spaghetti, clean!
                //catch encrypted database
                if (ex.NativeError == 25028)
                {
                    //if empty, get the pass
                    if (string.IsNullOrEmpty(dbPass))
                    {
                        GetPass gp = new GetPass();
                        if (gp.ShowDialog() == DialogResult.OK)
                        {
                            if (gp.edPass.Text != string.Empty)
                            {
                                dbPass = gp.edPass.Text;
                                OpenConnection(fileName);
                                return true;
                            }
                        }
                        else throw ex; //otherwise just re-throw

                    }
                    else //pass is wrong
                    {
                        MessageBox.Show("Unable to decrypt the database. Wrong password.");
                        return false;
                    }

                } //not encryption issue
                else throw ex;
                return false;

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error opening database\r\n\r\n" + ex.Message, "Error opening database!");
                return false;
            }

        }


        /// <summary>
        /// Closes the database connection.
        /// </summary>
        public static void CloseConnection()
        {
            if (dbConn.State != ConnectionState.Closed)
            {
                dbPass = string.Empty;
                dbConn.Close();

            }
        }

        /// <summary>
        /// Load db info to treenodes
        /// </summary>
        /// <param name="nodeTables">Node representing the tables</param>
        /// <param name="nodeIndexes">Node representing the indexes</param>
        public static void FillTreeInfo(TreeNode nodeTables, TreeNode nodeIndexes)
        {

            FillTablesNode(nodeTables);
            FillIndexNode(nodeIndexes);
        }


        /// <summary>
        /// Fill provided node with information about tables
        /// Also adds child node to each table so there would be + in treenode
        /// </summary>
        /// <param name="nodeTables">Node representing the tables</param>
        private static void FillTablesNode(TreeNode nodeTables)
        {
            nodeTables.Nodes.Clear(); //shouldn't be needed!


            SqlCeDataReader reader = sqlGetTables.ExecuteReader();

            while (reader.Read())
            {
                TreeNode node = new TreeNode(reader.GetString(0), 4, 4, new TreeNode[1] { new TreeNode() });

                nodeTables.Nodes.Add(node);
            }
            reader.Close();

        }

        /// <summary>
        /// Fill index information
        /// </summary>
        /// <param name="nodeIndexes">Node representing the indexes</param>
        private static void FillIndexNode(TreeNode nodeIndexes)
        {
            nodeIndexes.Nodes.Clear(); //not needed, but just in case...


            SqlCeDataReader reader = sqlGetIndexes.ExecuteReader();


            while (reader.Read())
            {
                TreeNode node = new TreeNode(reader.GetString(0), 4, 4);



                node.ToolTipText = string.Format("Table: {0}\r\nColumn: {1}", reader.GetString(1), reader.GetString(2));

                if (reader.GetBoolean(3))
                {
                    node.ToolTipText += "\r\nPrimary key";
                    node.ImageIndex = 3;
                    node.SelectedImageIndex = 3;
                }
                nodeIndexes.Nodes.Add(node);
            }
            reader.Close();

        }


        /// <summary>
        /// Get general info about the database
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDatabaseInfo()
        {

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Name"));
            dt.Columns.Add(new DataColumn("Info"));

            try
            {

                dt.Rows.Add("File", dbFilename);
                dt.Rows.Add("File size", GetFileSize(new FileInfo(dbFilename).Length));
                dt.Rows.Add("Encrypted", !string.IsNullOrEmpty(dbPass) ? "Yes" : "No");

                SqlCeCommand cmd = dbConn.CreateCommand();

                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES";
                dt.Rows.Add("Tables", cmd.ExecuteScalar().ToString());

                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.INDEXES";
                dt.Rows.Add("Indexes", cmd.ExecuteScalar().ToString());

                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
                dt.Rows.Add("Keys", cmd.ExecuteScalar().ToString());

                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS";
                dt.Rows.Add("Table constraints", cmd.ExecuteScalar().ToString());

                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS";
                dt.Rows.Add("Foreign constraints", cmd.ExecuteScalar().ToString());


            }
            catch (Exception ex)
            {
                MessageBox.Show("Error getting database information\r\n\r\n" + ex.Message);

            }


            return dt;

        }

        /// <summary>
        /// Get file size as string.
        /// Found from internets, no idea where, been sitting in my toolbox forever.
        /// </summary>
        /// <param name="Bytes"></param>
        /// <returns></returns>
        private static string GetFileSize(long Bytes)
        {
            if (Bytes >= 1073741824)
            {
                Decimal size = Decimal.Divide(Bytes, 1073741824);
                return String.Format("{0:##.##} GB", size);
            }
            else if (Bytes >= 1048576)
            {
                Decimal size = Decimal.Divide(Bytes, 1048576);
                return String.Format("{0:##.##} MB", size);
            }
            else if (Bytes >= 1024)
            {
                Decimal size = Decimal.Divide(Bytes, 1024);
                return String.Format("{0:##.##} KB", size);
            }
            else if (Bytes > 0 & Bytes < 1024)
            {
                Decimal size = Bytes;
                return String.Format("{0:##.##} bytes", size);
            }
            else
            {
                return "0 bytes";
            }
        }


        /// <summary>
        /// Get information about all tables
        /// </summary>
        /// <returns></returns>
        public static SqlCeResultSet GetAllTableInfo()
        {
            return sqlGetAllTablesInfo.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        /// <summary>
        /// Get information about all indexes
        /// </summary>
        /// <returns></returns>
        public static SqlCeResultSet GetAllIndexesInfo()
        {
            return sqlGetAllIndexesInfo.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        /// <summary>
        /// Information about all keys
        /// </summary>
        /// <returns></returns>
        public static SqlCeResultSet GetAllKeysInfo()
        {
            return sqlGetAllKeysInfo.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        /// <summary>
        /// Information about all constraints
        /// </summary>
        /// <returns></returns>
        public static SqlCeResultSet GetAllConstraintsInfo()
        {
            return sqlGetAllConstraintsInfo.ExecuteResultSet(ResultSetOptions.Scrollable);
        }

        /// <summary>
        /// Get information about a table
        /// </summary>
        /// <param name="nodeText"></param>
        /// <returns></returns>
        public static SqlCeResultSet LoadTableData(string nodeText)
        {
            //cannot be prepared
            SqlCeCommand sqlGetTableData = dbConn.CreateCommand();
            sqlGetTableData.CommandText = string.Format("SELECT * FROM [{0}]", nodeText);

            return sqlGetTableData.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive);

        }

        /// <summary>
        /// Get column type
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static string GetColumnType(string tableName, string columnName)
        {



            getColumnType.Parameters.Clear();
            getColumnType.Parameters.Add(getColumnType.CreateParameter());
            getColumnType.Parameters.Add(getColumnType.CreateParameter());

            getColumnType.Parameters[0].Value = tableName;
            getColumnType.Parameters[1].Value = columnName;


            SqlCeDataReader reader = getColumnType.ExecuteReader();

            string s = string.Empty;
            //one record only!
            while (reader.Read())
            {
                if (!reader.IsDBNull(2))
                {
                    s = string.Format("Data type: {0}\r\nMax length: {1}", reader.GetString(1), reader.GetInt32(2));

                }
                else
                {
                    s = string.Format("Data type: {0}\r\nPrecision: {1}\r\nScale: {2}", reader.GetString(1), reader.GetValue(3), reader.GetValue(4));
                }
            }

            reader.Close();

            return s;


        }

        /// <summary>
        /// Get the info about the index
        /// </summary>
        /// <param name="indexName"></param>
        /// <returns></returns>
        public static SqlCeResultSet GetIndexInfo(string indexName)
        {

            getIndexInfo.Parameters.Clear();
            getIndexInfo.Parameters.Add(getIndexInfo.CreateParameter());
            getIndexInfo.Parameters[0].Value = indexName;

            return getIndexInfo.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);

        }

        /// <summary>
        /// Execute manual query
        /// Error handling is done elsewhere, mostly.
        /// Rewrite this at some point, spaghetti feature creep
        /// </summary>
        /// <param name="queryText"></param>
        /// <returns></returns>
        public static SqlCeResultSet ExecuteManualQuery(string queryText, out Exception ex, bool isReapeatingQuery)
        {            

            ex = null;

            if (!isConnectionOpen)
                return null;

            SqlCeCommand sqlQuery = dbConn.CreateCommand();
            sqlQuery.CommandText = queryText;




            try
            {

                ResultSetOptions rsOptions = ResultSetOptions.Scrollable;

                if (!isReapeatingQuery)
                    rsOptions = ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive;
                else
                    rsOptions = ResultSetOptions.Scrollable;
                SqlCeResultSet set = sqlQuery.ExecuteResultSet(rsOptions);

                if (set.Scrollable)
                    return set;
                else
                {
                    set.Close();
                    return null;
                }




            }
            catch (SqlCeException exp)
            {
                if (ex != null)
                {
                    ex = exp;
                    return null;
                }

                ex = exp;
                //cursor not updatable. How many more of those errors exist, time to do array soon...
                if ((exp.NativeError == 25604) || (exp.NativeError == 25607) || (exp.NativeError == 25612))
                {

                    return ExecuteManualQuery(queryText, out ex, true);
                }

                return null;
            }

        }


        /// <summary>
        /// Fill column nodes
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        public static SqlCeResultSet GetTableColumns(TreeNode node)
        {
            if (!isConnectionOpen)
                return null;

            node.Nodes.Clear();


            getAllColumns.Parameters.Clear();
            getAllColumns.Parameters.Add(getColumnType.CreateParameter());
            getAllColumns.Parameters.Add(getColumnType.CreateParameter());
            getAllColumns.Parameters[1].Value = getAllColumns.Parameters[0].Value = node.Text;

            
            SqlCeResultSet resultSet = getAllColumns.ExecuteResultSet(ResultSetOptions.Scrollable);
           
            //fill individual nodes
            while (resultSet.Read())
            {
                TreeNode newNode = new TreeNode(resultSet.GetString(resultSet.GetOrdinal("COLUMN_NAME")), 6, 6);

                //primary key
                if (!resultSet.IsDBNull(0) && resultSet.GetBoolean(0))
                {
                    newNode.SelectedImageIndex = 3;
                    newNode.ImageIndex = 3;

                }

                if (!resultSet.IsDBNull(resultSet.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")))
                {

                    newNode.ToolTipText = string.Format("Data type: {0}\r\nMax length: {1}", resultSet.GetString(resultSet.GetOrdinal("DATA_TYPE")), resultSet.GetInt32(resultSet.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")));

                }
                else
                {
                    newNode.ToolTipText = string.Format("Data type: {0}\r\nPrecision: {1}\r\nScale: {2}", resultSet.GetString(resultSet.GetOrdinal("DATA_TYPE")), resultSet.GetValue(resultSet.GetOrdinal("NUMERIC_PRECISION")), resultSet.GetValue(resultSet.GetOrdinal("NUMERIC_SCALE")));

                }

                if (!resultSet.IsDBNull(resultSet.GetOrdinal("COLUMN_DEFAULT")))
                {
                    newNode.ToolTipText += string.Format("\r\nDefault: {0}", resultSet.GetValue(5));
                }


                node.Nodes.Add(newNode);
            }         
            
            return resultSet;


        }


    }
}
